import { ALL, Body, Inject, Post, Provide } from '@midwayjs/decorator';
import { CoolController, BaseController, BaseService } from 'midwayjs-cool-core';
import { financeEntity } from '../../entity/finance';
import { studentEntity } from '../../../student/entity/student'
import { FinanceService } from '../../service/finance';
import { Context } from 'vm';
/**
 * 描述
 */
@Provide()
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: financeEntity,
  service: FinanceService,
  before: ctx => {
    // 将前端的数据转JSON格式存数据库
    // const { data } = ctx.request.body;
    let data = ctx.request.body;

    ctx.request.body.data = JSON.stringify(data);
  },
  pageQueryOp: {
    keyWordLikeFields: ['b.name', 'b.student_id'],
    // 指定返回字段
    select: ['a.*', 'b.student_id', 'b.name'],
    leftJoin: [{
      entity: studentEntity,
      alias: 'b',
      condition: 'a.student = b.id'
    }],
    where: async (ctx: Context) => {
      console.log(ctx.request, '+++');

      const body_data = JSON.parse(ctx.request.body.data)
      let ret = []
      console.log(body_data, '---');

      if (body_data.into_date) {
        let start_time = body_data.into_date[0]
        let end_time = body_data.into_date[1]
        ret.push(['a.into_date >= :start', { start: start_time }],
          ['a.into_date <= :end', { end: end_time }])
      }
      ret.push(['a.isDelete = :isDelete', { isDelete: false }])
      return ret
    }
  },
  listQueryOp: {
    // 让type字段支持筛选
    fieldEq: ['student'],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    },
  }
})
export class financeAdminController extends BaseController {
  @Inject()
  service: BaseService
  /**
 * 报表，统计在读学生和未读学生
 * @param query
 */
  @Post('/into_finance')
  async restAllClass(@Body(ALL) queryObject: any) {
    let year = queryObject.year;
    let money_list = []
    let into_num_list = []
    let common_num_list = []
    let special_num_list = []

    function formatZero(num, len) {
      if (String(num).length > len) return num;
      return (Array(len).join("0") + num).slice(-len);
    }
    for (let index = 1; index <= 12; index++) {
      const money = await this.service.nativeQuery(
        `
          SELECT IFNULL(sum(money),0) as money from finance 
           where isDelete = FALSE and into_date LIKE "%${year}-${formatZero(index, 2)}%"
           `
      )
      money_list.push(money[0].money)

    }
    for (let index = 1; index <= 12; index++) {
      const into_num = await this.service.nativeQuery(
        `
         SELECT count(id) as money from finance 
          where isDelete = FALSE and into_date LIKE "%${year}-${formatZero(index, 2)}%"
          `
      )
      into_num_list.push(into_num[0].money)

    }
    for (let index = 1; index <= 12; index++) {
      const common_num = await this.service.nativeQuery(
        `
         SELECT IFNULL(sum(common_num),0) as common_num  from finance 
          where isDelete = FALSE and into_date LIKE "%${year}-${formatZero(index, 2)}%"
          `
      )
      common_num_list.push(common_num[0].common_num)

    }
    for (let index = 1; index <= 12; index++) {
      const special_num = await this.service.nativeQuery(
        `
         SELECT IFNULL(sum(special_num),0) as special_num from finance 
          where isDelete = FALSE and into_date LIKE "%${year}-${formatZero(index, 2)}%"
          `
      )
      special_num_list.push(special_num[0].special_num)

    }
    

    return {
      code: 1000, data: {
        money: money_list,
        into_num: into_num_list,
        common_num: common_num_list,
        special_num: special_num_list,
      }
    }
  }

}
